/**
 * Copyright(c)2012 Beijing PeaceMap Co.,Ltd.
 * All right reserved. 
 */
package com.pmc.dwa.common.dialect;

/**
 * @description  SqlServer方言【摘自hibernate】
 * @author aokunsang
 * @date 2013-1-6
 */
public class SqlServerDialect extends Dialect{

	 /***
     * Add a LIMIT clause to the given SQL SELECT The LIMIT SQL will look like:
     * 
     * <pre>
     * WITH query AS (SELECT ROW_NUMBER() OVER (ORDER BY orderby) as __hibernate_row_nr__, original_query_without_orderby)
     * SELECT * FROM query WHERE __hibernate_row_nr__ BEETWIN offset AND offset + last
     * --ORDER BY __hibernate_row_nr__: Don't think that wee need this last order by clause
     * </pre>
     * 
     ***/
     public String getLimitString(String sql, int offset, int limit) {
        if (offset <= 0) {
            return getSingleLimitString(sql, offset, limit);
        }

        StringBuilder sb = new StringBuilder(sql.trim());

        String querySqlLowered = sql.trim().toLowerCase();
        int orderByIndex = querySqlLowered.toLowerCase().indexOf("order by");
        String orderby = orderByIndex > 0 ? sql.substring(orderByIndex) : "ORDER BY CURRENT_TIMESTAMP";

        // Delete the order by clause at the end of the query
        if (orderByIndex > 0) {
            sb.delete(orderByIndex, orderByIndex + orderby.length());
        }

        // Find the end of the select statement
        int selectIndex = querySqlLowered.trim().indexOf(SqlServerDialect.SELECT_DISTINCT);
        if (selectIndex != -1) {
            selectIndex += SqlServerDialect.SELECT_DISTINCT.length();
        } else {
            selectIndex = querySqlLowered.trim().indexOf(SqlServerDialect.SELECT);
            if (selectIndex != -1) {
                selectIndex += SqlServerDialect.SELECT.length();
            }
        }

        // Insert after the select statement the row_number() function:
        sb.insert(selectIndex, " ROW_NUMBER() OVER (" + orderby + ") as __hibernate_row_nr__,");

        //Wrap the query within a with statement:
        sb.insert(0, "WITH query AS (").append(") SELECT * FROM query ");
        sb.append("WHERE __hibernate_row_nr__ BETWEEN ").append(offset + 1).append(" AND ").append(limit + offset + 1);

        return sb.toString();
    }
    /**
     * 查询的第一页数据，使用top语句 
     * @param querySelect
     * @param offset
     * @param limit
     * @return
     */
    private String getSingleLimitString(String querySelect, int offset, int limit) {
 		if ( offset > 0 ) {
 			throw new UnsupportedOperationException( "query result offset is not supported" );
 		}
  		return new StringBuffer( querySelect.length() + 8 )
				.append( querySelect )
 				.insert( getAfterSelectInsertPoint( querySelect ), " top " + limit )
 				.toString();
	}
    
    private int getAfterSelectInsertPoint(String sql) {
 		int selectIndex = sql.toLowerCase().indexOf(SqlServerDialect.SELECT);
 		final int selectDistinctIndex = sql.toLowerCase().indexOf(SqlServerDialect.SELECT_DISTINCT);
		return selectIndex + ( selectDistinctIndex == selectIndex ? 15 : 6 );
	}
}
